package cn.exam.sys.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Random;

import cn.exam.common.DBUnitHelper;
import cn.exam.common.PageControl;
import cn.exam.common.Pager;
import cn.exam.model.Paper;
import cn.exam.model.Subject;
import cn.exam.sys.dao.interfaces.IPaperDao;

public class PaperDao implements IPaperDao {
	//新增试卷
	@Override
	public Integer addpaper(Paper paper) {
		String pNo=String.valueOf(System.currentTimeMillis());
		// TODO Auto-generated method stub
		String sql = "INSERT INTO paper(pname, pno, sid, seq, difficulty,category) " +
		" SELECT ?,?,t.sid, @rownum:=@rownum+1,?,? FROM (SELECT @rownum:=0,s.sid FROM subject s where s.sstate = 1 ORDER BY rand() LIMIT ? ) t";
		//新增获取试卷难度
		String difficulty = paper.getDifficulty();
		//新增获取试卷分类
		String category = paper.getCategory();
		//修改在执行更新时传递难度信息
		Integer rtn =DBUnitHelper.executeUpdate(sql,paper.getPname(),pNo,difficulty,category,paper.getScount());
		
		return rtn;
	}

	public Pager<Paper> list(Paper paper,PageControl pc) {
		// 修改 SQL 查询语句，添加 ORDER BY pid DESC
		String sql = "SELECT max(pid) pid,pno,pname,count(*) scount,difficulty,category  FROM paper GROUP BY pname,pno,difficulty,category ORDER BY pid DESC" ;
		Pager<Paper> pager;
		String pid="pid";
		if(paper.getPname()!=null && !paper.getPname().equals("")){
			String sql2 = " where pname LIKE '%' "+" ? "+" '%' ";
			// 修改 SQL 查询语句，添加 ORDER BY pid DESC
			String sql3 = "SELECT pname,count(*) scount FROM paper " +sql2+" GROUP BY pname ORDER BY pid DESC"; 
//			String sql3 = sql.substring(0,44) + sql2 + sql.substring(44);
			pager = DBUnitHelper.execlist(sql3, pc, Paper.class,pid, paper.getPname());
		}else{
			pager = DBUnitHelper.execlist(sql, pc, Paper.class,pid, null);	
		}
		return pager;
	}

	@Override
	public List<Subject> subjectlist(Paper paper) {
		// TODO Auto-generated method stub
		String sql = "SELECT subject.sid,scontent,sa,sb,sc,sd,skey,seq FROM subject,paper WHERE paper.sid = subject.sid and paper.pno = ? order by paper.seq";
		List<Subject> list = DBUnitHelper.executeQuery(sql,Subject.class, paper.getPno());
		return list;
	}

	@Override
	public List<Paper> list(Paper paper) {
		// TODO Auto-generated method stub
		String sql = "SELECT pname,pno,count(*) scount FROM paper GROUP BY pname,pno" ;
		List<Paper> list = DBUnitHelper.executeQuery(sql,Paper.class);
		return list;
	}

//	 新增手动添加试题到试卷的方法

	@Override
	public void addSubjectToPaper(Paper paper, Integer subjectId) {
		String sql = "insert into paper(pname,pno,sid,seq,difficulty,category) values (?,?,?,?,?,?)";
		String pNo = paper.getPno();
		String pName = paper.getPname();
		String difficulty = paper.getDifficulty();
		String category = paper.getCategory();
		int seq = 1;
		DBUnitHelper.executeUpdate(sql,pName,pNo,subjectId,seq,difficulty,category);
	}

	 // 删除试卷功能

	@Override
	public Integer deletePaper(String pno) {
		String sql ="delete from paper where pno=?";
		try(Connection conn=DBUnitHelper.getConn();
			PreparedStatement pstmt = conn.prepareStatement(sql)){
			pstmt.setString(1,pno);
			return pstmt.executeUpdate();
		}catch (SQLException e){
			e.printStackTrace();
			return 0;
		}
	}

}
